﻿--use PolePosition
GO

IF EXISTS (SELECT *  FROM sys.objects  WHERE object_id = OBJECT_ID(N'fn_DistributionPole_GetPhaseString') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	DROP FUNCTION fn_DistributionPole_GetPhaseString

GO
CREATE FUNCTION fn_DistributionPole_GetPhaseString
(
	  @DistributionPoleId int
	 ,@IsAsset bit
)
RETURNS varchar(MAX)
AS
BEGIN

	DECLARE @Join varchar(MAX)
	SET @Join = ''
	
	IF (@IsAsset = 1)
	BEGIN
		SELECT 
			@Join = @Join + Phase.[Name] + ', '
		FROM tb_Utility_Asset_Distribution_PhasesXPole XPole
		INNER JOIN tb_Distribution_Phases Phase
			ON XPole.PhaseFk = Phase.PhaseId
		WHERE XPole.Utility_Asset_DistributionFk = @DistributionPoleId
	END	
	ELSE
	BEGIN
		SELECT 
			@Join = @Join + Phase.[Name] + ', '
		FROM tb_Vendor_Distribution_PhasesXPole XPole
		INNER JOIN tb_Distribution_Phases Phase
			ON XPole.Vendor_Distribution_PhaseFk = Phase.PhaseId
		WHERE XPole.Vendor_Distribution_PoleFk = @DistributionPoleId
	END

	IF (@Join <> '' AND NOT @Join IS NULL)
		SET @Join = LEFT(@Join, LEN(@Join) - 1) 

	RETURN @Join

END

GO
